from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref


app = Flask(__name__)
# 连接数据库连接url
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://zhangyz:123456@10.4.7.13:3306/school?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# 查询时会显示原始SQL语句
app.config["SQLALCHEMY_ECHO"] = True

# 把SQLAlchemy组件注册到项目中
db = SQLAlchemy()
db.init_app(app)


class Student(db.Model):
    __tablename__ = "t_1v1_student"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(15), index=True, comment="姓名")
    age = db.Column(db.SmallInteger, comment="年龄")
    sex = db.Column(db.Boolean, default=True, comment="性别")
    email = db.Column(db.String(128), unique=True, comment="邮箱地址")
    money = db.Column(db.Numeric(10, 2), default=0.0, comment="钱包")
    info = db.relationship("StudentInfo", uselist=False, backref=backref("student", uselist=False))
    address_list = db.relationship("StudentAddress", uselist=True, backref=backref("student", uselist=False), lazy="dynamic")
    # 关联属性：1对多
    # 外键模型：主模型 StudentAddress.student 获取结果的时候是一个模型对象
    # 主模型：外键模型 Student.address_list 获取结果的时候是一个列表

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"


class StudentInfo(db.Model):
    __tablename__ = "t_1v1_student_info"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    # 外键字段（提供给数据库）
    student_id = db.Column(db.Integer, db.ForeignKey("t_1v1_student.id"), comment="student外键")
    address = db.Column(db.String(255), comment="注册地址")
    mobile = db.Column(db.String(11), unique=True, comment="手机号码")

    def __repr__(self):
        return f"<{self.student.name} {self.__class__.__name__}>"


class StudentAddress(db.Model):
    __tablename__ = "t_1vn_student_address"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(255), default="默认", comment="名称")
    province = db.Column(db.String(255), comment="省份")
    city = db.Column(db.String(50), comment="城市")
    area = db.Column(db.String(50), comment="地区")
    address = db.Column(db.String(500), comment="详细地址")
    mobile = db.Column(db.String(15), comment="收货人电话")
    # 外键字段（提供给数据库）
    student_id = db.Column(db.Integer, db.ForeignKey("t_1v1_student.id"), comment="student外键")

    def __repr__(self):
        return f"<{self.student.name} {self.__class__.__name__}>"


@app.route("/add_data01")
def add_data01():
    """添加操作"""
    # 主模型已存在，添加外键模型
    student = Student(name="张小明", age=17, sex=True, email="zhangxiaoming@163.com", money=100000)
    db.session.add(student)
    db.session.commit()

    student.address_list.append(StudentAddress(name="公司", province="北京市", city="北京市", area="昌平区", address="xxxx大厦", mobile="13888888888"))
    db.session.commit()

    return "ok"

@app.route("/add_data02")
def add_data02():
    """添加操作 - 一次添加多条数据"""
    student = Student(name="张小白", age=15, sex=True, email="zhangxiaobai@163.com", money=100000)
    db.session.add(student)
    db.session.commit()

    student.address_list = [
        StudentAddress(name="学校", province="北京市", city="北京市", area="昌平区", address="xxxx大厦101", mobile="13888888888"),
        StudentAddress(name="加里", province="北京市", city="北京市", area="昌平区", address="xxxx大厦102", mobile="13888888888"),
        StudentAddress(name="公司", province="北京市", city="北京市", area="昌平区", address="xxxx大厦103", mobile="13888888888"),
    ]
    db.session.commit()

    return "ok"

@app.route("/add_data03")
def add_data03():
    """添加操作 - 添加多条数据和外键模型"""
    student = Student(
        name="张小红",
        age=30,
        sex=False,
        email="zhangxiaohong@163.com",
        money=100000,
        address_list=[
            StudentAddress(name="学校", province="北京市", city="北京市", area="昌平区", address="xxxx大厦201", mobile="13888888821"),
            StudentAddress(name="家里", province="北京市", city="北京市", area="昌平区", address="xxxx大厦202", mobile="13888888821"),
            StudentAddress(name="公司", province="北京市", city="北京市", area="昌平区", address="xxxx大厦203", mobile="13888888821"),
        ]
    )
    db.session.add(student)
    db.session.commit()

    return "ok"

@app.route("/add_data04")
def add_data04():
    """添加模型数据"""
    address = StudentAddress(
        name="公司",
        province="北京市",
        city="北京市",
        area="昌平区",
        address="xxxx大厦",
        mobile="13588888821",
        student=Student(
            name="张小三",
            age=18,
            sex=True,
            email="zhangxiaosan@163.com",
            money=100
        )
    )
    db.session.add(address)
    db.session.commit()

    return "ok"

@app.route("/query_data01")
def query_data01():
    """查询操作"""
    student = Student.query.filter(Student.name=="张小白").first()
    print(student)
    print(student.address_list.all())

    return "ok"


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='0.0.0.0', port=5000, debug=True)